package com.ambow.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.ambow.shop.dao.ProductDao;
import com.ambow.shop.domain.Product;
import com.ambow.shop.domain.ProductCategory;
import com.ambow.shop.util.JdbcUtils;

/**
 * 产品数据访问实现。
 * 
 * @author chunw
 *
 */
public class ProductDaoJdbcImpl implements ProductDao {
	@Override
	public int add(Product product) throws SQLException {
		Connection conn = JdbcUtils.getConnection();
		final String sql = "INSERT INTO product(name, description, price, imageFileName, productCategoryId) VALUES(?, ?, ?, ?, ?)";
		return new QueryRunner().execute(conn, sql, product.getName(), product.getDescription(), product.getPrice(),
				product.getImage(), product.getCategory().getId());
	}

	@Override
	public int delete(int id) throws SQLException {
		Connection conn = JdbcUtils.getConnection();
		final String sql = "DELETE FROM product WHERE id = ?";
		return new QueryRunner().execute(conn, sql, id);
	}

	@Override
	public int update(Product product) throws SQLException {
		Connection conn = JdbcUtils.getConnection();
		final String sql = "UPDATE product set name = ?, description = ?, price = ?, imageFileName = ?, productCategoryId=? WHERE id = ?";
		return new QueryRunner().execute(conn, sql, product.getName(), product.getDescription(), product.getPrice(),
				product.getImage(), product.getCategory().getId(), product.getId());

	}

	@Override
	public Product get(int id) throws SQLException {
		final String sql = "SELECT id, name, description, price, imageFileName, productCategoryId FROM product WHERE id = ?";
		Connection conn = JdbcUtils.getConnection();
		try (PreparedStatement stmt = conn.prepareStatement(sql)) {
			stmt.setInt(1, id);
			try (ResultSet rs = stmt.executeQuery();) {
				if (rs.next()) {
					int productId = rs.getInt(1);
					String productName = rs.getString(2);
					String productDescription = rs.getString(3);
					double productPrice = rs.getDouble(4);
					String productImage = rs.getString(5);
					int productCategoryId = rs.getInt(6);

					return new Product(productId, productName, productDescription, productPrice, productImage,
							new ProductCategory(productCategoryId, null, null));
				}
				return null;
			}
		}
	}

	@Override
	public List<Product> list(int startRow, int next) throws SQLException {
		final String sql = "SELECT id, name, description, price, imageFileName, productCategoryId FROM product LIMIT ?, ?";
		List<Product> products = new ArrayList<Product>();
		Connection conn = JdbcUtils.getConnection();
		try (PreparedStatement stmt = conn.prepareStatement(sql)) {
			stmt.setInt(1, startRow);
			stmt.setInt(2, next);
			try (ResultSet rs = stmt.executeQuery();) {
				while (rs.next()) {
					int productId = rs.getInt(1);
					String productName = rs.getString(2);
					String productDescription = rs.getString(3);
					double productPrice = rs.getDouble(4);
					String productImage = rs.getString(5);
					int productCategoryId = rs.getInt(6);
					products.add(new Product(productId, productName, productDescription, productPrice, productImage,
							new ProductCategory(productCategoryId, null, null)));
				}
			}
		}
		return products;
	}

	@Override
	public Long getTotalRows() throws SQLException {
		Connection conn = JdbcUtils.getConnection();
		final String sql = "SELECT count(*) FROM product";
		return new QueryRunner().query(conn, sql, new ScalarHandler<Long>());
	}

}